package com.guhappy.model.contest;

// Generated Jul 19, 2011 6:12:32 PM by Hibernate Tools 3.4.0.CR1

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;

import com.guhappy.action.model.PersonalStockList;
import com.guhappy.util.HibernateListener;

/**
 * Home object for domain model class Contests.
 * @see  bd
 * @author  B.D
 */
public class ContestHome {

	private static final Logger log = Logger.getLogger(ContestHome.class);

	private static final SessionFactory sessionFactory = getSessionFactory();

	protected static SessionFactory getSessionFactory() {
		SessionFactory sessionFactory = 
	         (SessionFactory) ServletActionContext.getServletContext()
                    .getAttribute(HibernateListener.KEY_NAME);
		return sessionFactory;
	}

	public static void persist(Contest transientInstance) {
		log.debug("persisting Contests instance");
		try {
			sessionFactory.getCurrentSession().persist(transientInstance);
			log.debug("persist successful");
		} catch (RuntimeException re) {
			log.error("persist failed", re);
			throw re;
		}
	}

	public static void attachDirty(Contest instance) {
		log.debug("attaching dirty Contests instance");
		try {
			sessionFactory.getCurrentSession().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public static void attachClean(Contest instance) {
		log.debug("attaching clean Contests instance");
		try {
			sessionFactory.getCurrentSession().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public static void delete(Contest persistentInstance) {
		log.debug("deleting Contests instance");
		try {
			sessionFactory.getCurrentSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public static Contest merge(Contest detachedInstance) {
		log.debug("merging Contests instance");
		try {
			Contest result = (Contest) sessionFactory.getCurrentSession()
					.merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	public static Contest findById(java.lang.String id) {
		log.debug("getting Contests instance with id: " + id);
		Session session=null;
		Contest stock=null;
		try{
			session = sessionFactory.getCurrentSession();
			Query q = session.createQuery("from com.guhappy.model.user.Contest c where c.id = :id");
			q.setString("id", id);
			stock=(Contest) q.uniqueResult();
		}
		catch (Exception e){
			System.out.println(e);
			
		}
		return stock;
	}
	
	public static Contest findByTicker(String ticker){
		Contest stock = null;
		stock = (Contest) sessionFactory.getCurrentSession().createCriteria(Contest.class).add(Restrictions.eq("sticker", ticker.trim())).uniqueResult();
		return stock;
	}
	
	@SuppressWarnings("unchecked")
	public static GeneralContestView getContNextViewTree(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONT_NEXT_THREE");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT b.stock_ticker from VW_CONTSTOCK_NEXT_THREE").append(direction).append(" a, global_stocks b where upper(a.CONTESTID) = upper('").append(contestview.getId()).append("') and a.STOCKID = b.stock_id ");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	
	public static GeneralContestView getContNextViewWeekly(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONT_NEXT_WEEK");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT b.stock_ticker from VW_CONTSTOCK_NEXT_WEEK").append(direction).append(" a, global_stocks b where upper(a.CONTESTID) = upper('").append(contestview.getId()).append("') and a.STOCKID = b.stock_id ");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	public static GeneralContestView getContNextViewMonthly(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONT_NEXT_MON");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT b.stock_ticker from VW_CONTSTOCK_NEXT_MON").append(direction).append(" a, global_stocks b where upper(a.CONTESTID) = upper('").append(contestview.getId()).append("') and a.STOCKID = b.stock_id ");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	public static GeneralContestView getConNextViewQuarter(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONT_NEXT_QUART");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT b.stock_ticker from VW_CONTSTOCK_NEXT_QUART").append(direction).append(" a, global_stocks b where upper(a.CONTESTID) = upper('").append(contestview.getId()).append("') and a.STOCKID = b.stock_id ");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	public static GeneralContestView getContNextViewYear(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONT_NEXT_YEAR");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT b.stock_ticker from VW_CONTSTOCK_NEXT_YEAR").append(direction).append(" a, global_stocks b where upper(a.CONTESTID) = upper('").append(contestview.getId()).append("') and a.STOCKID = b.stock_id ");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	
	
	public static GeneralContestView getContestViewTree(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONTEST_THREE_");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("three_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
		buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_three").append(direction).append(" a, global_stocks b where upper(a.contestid) = upper('");
		buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
		buf2.append(") s)t");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	
	public static GeneralContestView getContestViewWeekly(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONTEST_WEEKLY_");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){		
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("weekly_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
		buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_weekly").append(direction).append(" a, global_stocks b where upper(a.contestid) = upper('");
		buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
		buf2.append(") s)t");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	public static GeneralContestView getContestViewMonthly(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONTEST_MONTHLY_");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){		
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("monthly_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
		buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_monthly").append(direction).append(" a, global_stocks b where upper(a.contestid) = upper('");
		buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		buf2.append(") s)t");
		return contestview;
	}
	public static GeneralContestView getContestViewQuarter(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONTEST_QUARTERLY_");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){		
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("quarterly_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
		buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_quarterly").append(direction).append(" a, global_stocks b where upper(a.contestid) = upper('");
		buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
		buf2.append(") s)t");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	public static GeneralContestView getContestViewYear(String region, String direction){
		GeneralContestView contestview = new GeneralContestView();
		StringBuffer buf = new StringBuffer();
		buf.append("select GH_CONTEST_STARTDATE, GH_CONTEST_ENDDATE,GH_CONTEST_PREMIUM,TOTAL_PLAYER, TOTAL_PICKS,TOTAL_STOCKS,GH_CONTEST_ID from VW_CONTEST_YEARLY_");
		buf.append(direction);
		if (null!=region && region.trim()!="")
			buf.append(" where f_regionid = ").append(Integer.parseInt(region));
		ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){		
			contestview.setContest_start_time((Date) rset.getDate(0));
			contestview.setContest_end_time((Date) rset.getDate(1));
			contestview.setPremium(((BigDecimal)rset.get(2)).floatValue());
			contestview.setTotal_players(((BigDecimal)rset.get(3)).intValue());
			contestview.setTotal_pool_size(((BigDecimal)rset.get(4)).intValue()*contestview.getPremium());
			contestview.setTotal_stocks(((BigDecimal)rset.get(5)).intValue());
			contestview.setContest_enroll_time(new Date(contestview.getContest_start_time().getTime()-1));
			contestview.setId( rset.getString(6));
			contestview.setContestType("yearly_"+direction);
		}
		StringBuffer buf2 = new StringBuffer();
		buf2.append("SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
		buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_yearly").append(direction).append(" a, global_stocks b where upper(a.contestid) = upper('");
		buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
		buf2.append(") s)t");
		contestview.setStockList(sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).list());
		return contestview;
	}
	
	public static List<ContestPersonalViewBean> buildPersonalContestList(String customerId){
		List<ContestPersonalViewBean> resultList = new ArrayList<ContestPersonalViewBean>();
		ScrollableResults rset = null;
		StringBuffer buf = new StringBuffer();
		StringBuffer buf2 = new StringBuffer();
		GeneralContestView contestview = new GeneralContestView();
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_three_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewTree(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_threeup").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("ThreeUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_three_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewTree(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_threedown").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("ThreeDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid from gh_contest_players a left join vw_contest_weekly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewWeekly(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_weeklyup").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("WeeklyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_weekly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewWeekly(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_weeklydown").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("WeeklyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_monthly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewMonthly(rset.getInteger(1).toString(),"UP") ;
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_monthlyup").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("MonthlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_monthly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewMonthly(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_monthlydown").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("MonthlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewQuarter(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_quarterlylyup").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("QuarterlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewQuarter(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_quarterlylydown").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("QuarterlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewYear(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_yearlyup").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("YearlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewYear(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER,v.r from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append("(SELECT t.* FROM (SELECT ROWNUM r,s.* FROM ( ");
				buf2.append("select b.stock_id, B.STOCK_TICKER, (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE from vw_contstock_yearlydown").append(" a, global_stocks b where upper(a.contestid) = upper('");
				buf2.append(contestview.getId()).append("') and A.STOCKID=B.STOCK_ID order by (B.STOCK_CURR_PRICE-A.STOCK_HISTORY_CLOSE)/a.STOCK_HISTORY_CLOSE desc");
				buf2.append(") s)t) v where u.f_stockid=v.stock_id");
				resultList.add(new ContestPersonalViewBean("YearlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		return resultList;
	}
	
	public static List<ContestPersonalViewBean> buildPersonalNextContestList(String customerId){
		List<ContestPersonalViewBean> resultList = new ArrayList<ContestPersonalViewBean>();
		ScrollableResults rset = null;
		StringBuffer buf = new StringBuffer();
		StringBuffer buf2 = new StringBuffer();
		GeneralContestView contestview = new GeneralContestView();
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_cont_next_threeup b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContNextViewTree(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("ThreeUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_three_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewTree(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("ThreeDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid from gh_contest_players a left join vw_contest_weekly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewWeekly(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("WeeklyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_weekly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewWeekly(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("WeeklyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_monthly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewMonthly(rset.getInteger(1).toString(),"UP") ;
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("MonthlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_monthly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewMonthly(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("MonthlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewQuarter(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("QuarterlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewQuarter(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("QuarterlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_up b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewYear(rset.getInteger(1).toString(),"UP");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("YearlyUp",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		buf.append("select a.f_customerid, b.F_REGIONID from gh_contest_players a left join vw_contest_quarterly_down b on a.gh_contest_id = b.gh_contest_id where upper(a.f_customerid) = upper('").append(customerId).append("') and b.gh_contest_id is not null");
		rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
		while (rset.next()){
			if (null!= rset.getString(0)){
				contestview = getContestViewYear(rset.getInteger(1).toString(),"Down");
				buf2.append("select v.STOCK_TICKER from (select b.f_stockid from gh_contest_players a , gh_contest_stocks b where a.GH_CONTPLAYER_ID=b.GH_CONTPLAYER_ID and upper(A.F_CUSTOMERID) = upper('").append(customerId).append("') and upper(A.GH_CONTEST_ID) = upper('").append(contestview.getId()).append("') ) u,");
				buf2.append(", global_stocks v where u.f_stockid = v.stock_id");
				resultList.add(new ContestPersonalViewBean("YearlyDown",contestview, (List<PersonalStockList>) sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll()));
			}
		}
		buf.delete(0,buf.length());
		buf2.delete(0,buf.length());
		
		return resultList;
	}
}
